﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.Data.OleDb;

namespace access001   
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnConnectDb_Click(object sender, EventArgs e)
        {
            //Step1: Create a connection
            OleDbConnection myConn = null;
            try
            {
                string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\pets.mdb";
                myConn = new OleDbConnection(connectionString);
                myConn.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: Connection Failed", ex.Message);
                return;
            }
          
            txtBoxLog.AppendText("[Ok] Connect Access Database, pets.mdb\n");

            // Step2: Set query command
            string myQuery = "SELECT * FROM PetTable";
            OleDbCommand myCmd = new OleDbCommand(myQuery, myConn);

            //Step3-1: Use OleDbReader
            OleDbDataReader myReader = myCmd.ExecuteReader();
            txtBoxLog.AppendText("[Info] Reader Data :\n");
            txtBoxLog.AppendText("Depth: "+ myReader.Depth.ToString() +"\n");
            txtBoxLog.AppendText("FieldCount: " + myReader.FieldCount.ToString() + "\n");
            txtBoxLog.AppendText("HasRows: " + myReader.HasRows.ToString() + "\n");
            txtBoxLog.AppendText("IsColsed: " + myReader.IsClosed.ToString() + "\n");
            txtBoxLog.AppendText("----- PetTable Content: ---------"+ "\n");
            while (myReader.Read())
            {
                txtBoxLog.AppendText(myReader.GetInt32(0) +"," + myReader.GetString(01)
                    + "," + myReader.GetString(02).ToString() + "\n");
            }
            myReader.Close();
            txtBoxLog.AppendText("----- PetTable Content.End ------" + "\n");
            
            //Step3-2: Use OleDbDataAdapter
            OleDbDataAdapter myAdapter = new OleDbDataAdapter();
            myAdapter.SelectCommand = myCmd;
            DataSet myDataSet = new DataSet();
            myAdapter.Fill(myDataSet, "NewTable");
            myConn.Close();


            /*
             * Process Data 
             * 3 Ways to read data
             * (1) use DataReader Object to read
             * (2) use DataAdapter object to read and set value to DataSet object
             * (3) use OleDbCommand to execute SQL command.
             * 
             * -- DataReader --
             *    Use DataReader object to read read-only and in-only streams from Database via SELECT command's return value. 
             *    It provides read-only pointer where point to data with an index in database, forward only
             *    Only one line content in the memory.
             * 
             * The Cases:
             *   (1) no need cache data
             *   (2) the data is too large, and memory cannot load the whole data
             *   (3) forward only, read-only, fast visit data
             * 
             * 
             */

        }

    }
}
